package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.close;
import static cam.School.DataAccessObject.DAOUtil.prepareStatement;
import static cam.School.DataAccessObject.DAOUtil.toSqlDate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.ClassRoomDAO;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.UserDAO;
import cam.School.DataTransfertObject.CallResult;
import cam.School.DataTransfertObject.ClassRoomDTO;

public class ClassRoomDAOImpl implements ClassRoomDAO {

	// Constants
	// ----------------------------------------------------------------------------------

	private static final String QUERY_PARAMS_GET_ALL = "id, name, infrastructureCategoryId, numberOfSits,"
			+ " numberOfStudents, area, isDeleted, firstRegistration,"
			+ " lastUpdate, state";

	private static final String UPDATE_QUERY_PARAMS = "name = ?, infrastructureCategoryId = ?, "
			+ " numberOfSits = ?, numberOfStudents = ?, area = ?, firstRegistration = ?, "
			+ " lastUpdate = ?, state = ?";

	private static final String SQL_FIND_ALL = "SELECT " + QUERY_PARAMS_GET_ALL
			+ " FROM classroom WHERE isDeleted = 0";

	private static final String SQL_FIND_BY_ID = "SELECT "
			+ QUERY_PARAMS_GET_ALL
			+ " FROM classroom WHERE  id = ? AND isDeleted = 0";
	private static final String SQL_FIND_BY_NAME = "SELECT "
			+ QUERY_PARAMS_GET_ALL
			+ " FROM classroom WHERE name = ? AND isDeleted = 0";

	private static final String SQL_UPDATE_BY_ID = "UPDATE classroom SET "
			+ UPDATE_QUERY_PARAMS
			+ " WHERE id = ? AND isDeleted = 0";

	private static final String SQL_INSERT = "INSERT INTO user (email, password, firstname, lastname, birthdate) VALUES (?, MD5(?), ?, ?, ?)";

	private static final String SQL_DELETE = "DELETE FROM user WHERE id = ?";

	private static final String SQL_EXIST_EMAIL = "SELECT id FROM Userz WHERE email = ?";

	private static final String SQL_CHANGE_PASSWORD = "UPDATE user SET password = MD5(?) WHERE id = ?";

	

	// Vars
	// ---------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// Constructors
	// -------------------------------------------------------------------------------

	private static ClassRoomDTO map(ResultSet resultSet) throws SQLException {
		ClassRoomDTO classRoom = new ClassRoomDTO();
		classRoom.setId(resultSet.getLong("id"));
		classRoom.setName(resultSet.getString("name"));
		classRoom.setCategoryId(resultSet.getLong("infrastructureCategoryId"));
		classRoom.setNumberOfSits(resultSet.getInt("numberOfSits"));
		classRoom.setNumberOfStudents(resultSet.getLong("numberOfStudents"));
		classRoom.setArea(resultSet.getString("area"));
		classRoom.setDeleted(resultSet.getBoolean("isDeleted"));
		classRoom.setFirstRegistration(resultSet.getDate("firstRegistration"));
		classRoom.setLastUpdate(resultSet.getDate("lastUpdate"));
		classRoom.setState(resultSet.getString("state"));
		return classRoom;
	}
	
	
	
	

	/**
	 * Construct an User DAO for the given DAOFactory. Package private so that
	 * it can be constructed inside the DAO package only.
	 * 
	 * @param daoFactory
	 *            The DAOFactory to construct this ClassRoom DAO for.
	 */
	public ClassRoomDAOImpl(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	@Override
	public ClassRoomDTO getById(Long id) throws DAOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<ClassRoomDTO> getAll() throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<ClassRoomDTO> classRooms = new ArrayList<ClassRoomDTO>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				classRooms.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return classRooms;
	}
	
	

	@Override
	public void create(ClassRoomDTO classRoom) throws IllegalArgumentException,
			DAOException {
		// TODO Auto-generated method stub

	}

	@Override
	public CallResult update(ClassRoomDTO classRoom)
			throws IllegalArgumentException, DAOException {
		Object[] values = { classRoom.getName(), classRoom.getCategoryId(),
				classRoom.getNumberOfSits(), classRoom.getNumberOfStudents(),
				classRoom.getArea(), toSqlDate(classRoom.getLastUpdate()),
				classRoom.getState(), classRoom.getId() };

		try (Connection connection = daoFactory.getConnection();
				PreparedStatement statement = prepareStatement(connection,
						SQL_UPDATE_BY_ID, false, values);) {
			int affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				return new CallResult(406,
						"Updating classroom failed, no rows affected.");
			}
		} catch (SQLException e) {
			return new CallResult(406, e.toString());
		}
		return new CallResult(200, "success");
	}

	@Override
	public boolean delete(int classRoomId) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public ClassRoomDTO getByName(String classRoomName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ClassRoomDTO getByCategory(String categoryName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ClassRoomDTO getByState(boolean state) {
		// TODO Auto-generated method stub
		return null;
	}
}
